//
//  RichCore.m
//  Rich
//
//  Created by Kenny Chen on 13/1/28.
//  Copyright (c) 2013年 Kenny Chen. All rights reserved.
//

#import "RichCore.h"
#import "RichError.h"
#import "DateHelper.h"

#pragma mark - Rich Account Implementation

@implementation RichAccount

@synthesize accountId;
@synthesize name;
@synthesize type;
@synthesize currencyCode;
@synthesize initialBalance;
@synthesize balance;
@synthesize balanceDate;
@synthesize creditCardAPR;
@synthesize property;
@synthesize description;

- (id)copyWithZone:(NSZone *)zone
{
    RichAccount *account = [[[self class] allocWithZone:zone] init];
    
    account.accountId = self.accountId;
    account.name = self.name;
    account.type = self.type;
    account.currencyCode = self.currencyCode;
    account.initialBalance = self.initialBalance;
    account.balance = self.balance;
    account.balanceDate = self.balanceDate;
    account.creditCardAPR = self.creditCardAPR;
    account.property = self.property;
    account.description = self.description;
    
    return account;
}

@end

#pragma mark - Rich Currency

@implementation RichCurrency

@synthesize code;
@synthesize name;
@synthesize symbol;
@synthesize position;
@synthesize format;

@end

#pragma mark - Rich General Transaction Implementation

@implementation RichTransactionGeneral

@synthesize transId;
@synthesize date;
@synthesize categoryCode;
@synthesize accountFrom;
@synthesize accountTo;
@synthesize creditCardBillId;
@synthesize installmentPaymentTimes;
@synthesize installmentMade;
@synthesize installmentDownPayment;
@synthesize installmentRate;
@synthesize installmentPeriod;
@synthesize installmentTransId;
@synthesize commission;
@synthesize amount;
@synthesize property;
@synthesize numberOfReceipts;
@synthesize receiptNo;
@synthesize comment;

@end

#pragma mark - Rich Credit Card Bill Implementation

@implementation RichCreditCardBill

@synthesize billId;
@synthesize accountId;
@synthesize closingDate;
@synthesize dueDate;
@synthesize transactions;
@synthesize payments;
@synthesize newBalance;
@synthesize payment;

- (double)newBalance
{
    return [[RichDelegate core] totalAmountOfTransactions:transactions];
}

- (double)payment
{
    return [[RichDelegate core] totalAmountOfPayments:payments];
}

@end

#pragma mark - Rich Stock Transaction Implementation

@implementation RichTransactionStock

@synthesize transId;
@synthesize date;
@synthesize closingDate;
@synthesize type;
@synthesize stockCode;
@synthesize accountStock;
@synthesize accountSettlement;
@synthesize price;
@synthesize downPaymentPercent;
@synthesize numberOfShare;
@synthesize tradingTax;
@synthesize commission;
@synthesize interest;
@synthesize amount;
@synthesize comment;

@end

#pragma mark - Rich Fund Transaction Implementation

@implementation RichTransactionFund

@end

#pragma mark - Rich Core Implementation

@implementation RichCore

@synthesize databaseVersion;
@synthesize currencies;
@synthesize category;
@synthesize creditCardBills;

- (id)init
{
    self = [super init];
    
    if (self) {
        databaseVersion = 0;
        sqliteManager = [[SQLiteManager alloc] init];
        currencies = [[NSMutableDictionary alloc] init];
        category = [[NSMutableDictionary alloc] init];
        creditCardBills = [[NSMutableDictionary alloc] init];
        
        _currTransArray = nil;
        _currTransType = kTransactionTypeGeneral;
        _currTransCondition = nil;
        _currTransBegin = 0;
        _currTransLimit = 0;
        _currDescending = YES;
        
        NSArray  *documentsPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
        NSString *databaseFilePath = [[documentsPaths objectAtIndex:0] stringByAppendingPathComponent:RichDatabaseData];
        
        if ([[NSFileManager defaultManager] fileExistsAtPath:databaseFilePath]) {
            // Database already exists, open it and check correctness
            
            if ([sqliteManager open:RichDatabaseData] < 0) {
                [self release];
                return nil;
            }
            
            if (self.databaseVersion < RichDatabaseVersion) {
                // Backup existing incorrect database and create a new one
                NSError  *error;
                NSArray  *documentsPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
                NSString *databaseFilePath = [[documentsPaths objectAtIndex:0] stringByAppendingPathComponent:RichDatabaseData];
                NSString *backupPath = [[databaseFilePath stringByDeletingLastPathComponent] stringByAppendingPathComponent:RichDatabaseDataBackup];
                
                // Close opened database
                [sqliteManager close];
                
                // Delete existing backup database
                if ([[NSFileManager defaultManager] fileExistsAtPath:backupPath]) {
                    if ([[NSFileManager defaultManager] removeItemAtPath:backupPath error:&error] == NO) {
                        NSLog(@"%@", [error localizedDescription]);
                        [self release];
                        return nil;
                    }
                }
                
                if ([[NSFileManager defaultManager] copyItemAtPath:databaseFilePath toPath:backupPath error:&error] == NO) {
                    // Error while renaming database
                    NSLog(@"%@", [error localizedDescription]);
                    [self release];
                    return nil;
                }

                // TODO: [Core] alter tables or migrate data to new database
                self.databaseVersion = RichDatabaseVersion;
                
                return self;
            }
            else if (self.databaseVersion == RichDatabaseVersion) {
                // Database correctness check passed
                if ([self initCurrency] < 0) {
                    [self release];
                    return nil;
                }
                
                return self;
            }
            else {
                // TODO: [Core] Bad database version handling
                NSLog(@"Database version %ld is newer then current version", self.databaseVersion);
                [self release];
                return nil;
            }
        }
        
        // Create a new database
        if ([sqliteManager open:RichDatabaseData] < 0) {
            [self release];
            return nil;
        }
        
        if ([self createTables] < 0) {
            [self release];
            return nil;
        }
        
        // Add initial database version
        int64_t rowId = [sqliteManager insertColumns:@"version"
                                          withValues:[NSString stringWithFormat:@"%d", RichDatabaseVersion]
                                           intoTable:RichtableVersion];
        
        if (rowId < 0) {
            [self release];
            return nil;
        }
    
        if ([self initCurrency] < 0) {
            [self release];
            return nil;
        }
        
        if ([self createCategory:RootCategoryExpend name:RichLocaleString(@"Expend")] < 0) {
            [self release];
            return nil;
        }
        
        if ([self createCategory:RootCategoryIncome name:RichLocaleString(@"Income")] < 0) {
            [self release];
            return nil;
        }
        
        if ([self createCategory:RootCategoryTransfer name:RichLocaleString(@"Transfer")] < 0) {
            [self release];
            return nil;
        }
    }
    
    return self;
}

- (void)dealloc
{
    if ([sqliteManager isOpen]) [sqliteManager close];
    [sqliteManager release];
    sqliteManager = nil;
    
    [currencies removeAllObjects];
    [currencies release];
    currencies = nil;
    
    [category removeAllObjects];
    [category release];
    category = nil;
    
    databaseVersion = 0;
    
    [super dealloc];
}

#pragma mark - Database

- (int)createTables
{
    int result;
    
    result = [sqliteManager createTable:RichtableVersion
                      columnDefinitions:@"version INTEGER NOT NULL PRIMARY KEY"];
    
    result = [sqliteManager createTable:RichTableCurrency
                      columnDefinitions:@"code       VARCHAR(3)  NOT NULL PRIMARY KEY, "
                                        @"name       VARCHAR(50) NOT NULL UNIQUE, "
                                        @"symbol     VARCHAR(5)  NOT NULL, "
                                        @"position   INTEGER NOT NULL DEFAULT(0), "
                                        @"format     VARCHAR(16) NOT NULL"];
    
    result = [sqliteManager createTable:RichTableAccounts
                      columnDefinitions:@"id_pk            INTEGER      NOT NULL PRIMARY KEY, "
                                        @"name             VARCHAR(100) NOT NULL UNIQUE, "
                                        @"type             INTEGER      NOT NULL, "
                                        @"currency_code_fk VARCHAR(3)   NOT NULL, "
                                        @"initial_balance  REAL         NOT NULL DEFAULT(0), "
                                        @"balance          REAL         NOT NULL DEFAULT(0), "
                                        @"balance_date     DATETIME     NOT NULL DEFAULT(CURRENT_TIMESTAMP), "
                                        @"credit_card_apr  REAL         NOT NULL DEFAULT(0), "
                                        @"property         INTEGER      NOT NULL DEFAULT(0), "
                                        @"description      TEXT         NULL, "
                                        @"FOREIGN KEY(currency_code_fk) REFERENCES currency(code) ON UPDATE CASCADE ON DELETE RESTRICT"];
    
    result = [sqliteManager createTable:RichTableCategory
                      columnDefinitions:@"code INTEGER      NOT NULL PRIMARY KEY, "
                                        @"name VARCHAR(100) NOT NULL"];
    
    result = [sqliteManager createTable:RichTableCreditCardBill
                      columnDefinitions:@"id_pk                 INTEGER NOT NULL PRIMARY KEY, "
                                        @"account               INTEGER NOT NULL, "
                                        @"closing_date          DATETIME NOT NULL, "
                                        @"due_date              DATETIME NOT NULL"];
    
    result = [sqliteManager createTable:RichTableGeneralTransactions
                      columnDefinitions:@"id_pk                     INTEGER NOT NULL PRIMARY KEY, "
                                        @"date                      DATETIME NOT NULL DEFAULT (CURRENT_TIMESTAMP), "
                                        @"category_code_fk          INTEGER NOT NULL, "
                                        @"from_account_id_fk        INTEGER NOT NULL DEFAULT(0), "
                                        @"to_account_id_fk          INTEGER NOT NULL DEFAULT(0), "
                                        @"credit_card_bill_id_fk    INTEGER NOT NULL DEFAULT(0), "
                                        @"installment_payment_times INTEGER DEFAULT(0), "
                                        @"installment_down_payment  REAL DEFAULT(0), "
                                        @"installment_interest_rate REAL DEFAULT(0), "
                                        @"installment_period        INTEGER DEFAULT(0), "
                                        @"installment_made          INTEGER DEFAULT(0), "
                                        @"installment_trans_id      INTEGER DEFAULT(0), "
                                        @"amount                    REAL NOT NULL DEFAULT(0), "
                                        @"property                  INTEGER NOT NULL DEFAULT(0), "
                                        @"commission                REAL NOT NULL DEFAULT(0), "
                                        @"number_of_receipts        INTEGER NOT NULL DEFAULT(0), "
                                        @"receipt_no                VARCHAR(100), "
                                        @"comment                   TEXT, "
                                        @"FOREIGN KEY(category_code_fk) REFERENCES categories(code) ON UPDATE CASCADE ON DELETE RESTRICT"];
    
    result = [sqliteManager createTable:RichTableStockTransactions
                      columnDefinitions:@"id_pk                    INTEGER NOT NULL PRIMARY KEY, "
                                        @"date                     DATETIME NOT NULL DEFAULT (CURRENT_TIMESTAMP), "
                                        @"closing_date             DATETIME DEFAULT (CURRENT_TIMESTAMP + 172800), "
                                        @"type                     INTEGER NOT NULL DEFAULT(0), "
                                        @"stock_code               VARCHAR(30) NOT NULL, "
                                        @"stock_account_id_fk      INTEGER NOT NULL REFERENCES accounts ON UPDATE CASCADE ON DELETE RESTRICT, "
                                        @"settlement_account_id_fk INTEGER NOT NULL REFERENCES accounts ON UPDATE CASCADE ON DELETE RESTRICT, "
                                        @"price                    REAL NOT NULL DEFAULT(0), "
                                        @"down_payment_percent     REAL DEFAULT(0), "
                                        @"number_of_share          INTEGER NOT NULL DEFAULT(0), "
                                        @"trading_tax              REAL NOT NULL DEFAULT(0), "
                                        @"commission               REAL DEFAULT(0), "
                                        @"interest                 REAL DEFAULT(0), "
                                        @"amount                   REAL DEFAULT(0), "
                                        @"comment                  TEXT"];
    
    result = [sqliteManager createTable:RichTableFundTransactions
                      columnDefinitions:@"id_pk              INTEGER NOT NULL PRIMARY KEY"];
    // TODO: [Fund]
    
    return 0;
}

#pragma mark - Version

- (NSUInteger)databaseVersion
{
    if (databaseVersion)
        return databaseVersion;
    
    SQLMResultSet *rs;
    
    rs = [sqliteManager selectColumns:@"version" fromTable:RichtableVersion where:nil];
    
    if (rs == nil) return -ERROR_CORE_DB_QUERY;
    
    if ([rs dataCount] == 0) return 0;
    
    while ([rs next]) {
        databaseVersion = [rs intForColumnIndex:0];
    }

    [rs release];
    
    return databaseVersion;
}

- (void)setDatabaseVersion:(NSUInteger)version
{
    if (version > databaseVersion) {
        int result = [sqliteManager updateTable:RichtableVersion
                                     setColumns:[NSString stringWithFormat:@"version = %ld", version]
                                          where:[NSString stringWithFormat:@"version = %ld", databaseVersion]];
        if (result == RICH_OK)
            databaseVersion = version;
    }
}

#pragma mark - Currency

- (int)initCurrency
{
    /* TODO: array updated */
    NSArray *defaultCurrencyTable =
        /*   Code    Name                                       Symbol   Pos Format  */
        @[@[ @"NTD", RichLocaleString(@"New Taiwan Dollar"),    @"$",    @0, @"#,###" ],
          @[ @"CNY", RichLocaleString(@"China Yuan Renminbi"),  @"¥",    @0, @"#,###.##" ],
          @[ @"HKD", RichLocaleString(@"Hong Kong Dollar"),     @"$",    @0, @"#,###.##" ],
          @[ @"USD", RichLocaleString(@"United States Dollar"), @"$",    @0, @"#,###.##" ],
          @[ @"JPY", RichLocaleString(@"Japanese Yen"),         @"¥",    @0, @"#,###" ],
          @[ @"EUR", RichLocaleString(@"Euro"),                 @"€",    @0, @"#,###.##" ],
          @[ @"GBP", RichLocaleString(@"Pound Sterling"),       @"£",    @0, @"#,###.##" ],
          @[ @"MOP", RichLocaleString(@"Macau Pataca"),         @"$",    @0, @"#,###.##" ],
          @[ @"KRW", RichLocaleString(@"South Korean Won"),     @"₩",    @0, @"#,###" ],
          @[ @"THB", RichLocaleString(@"Thailand Baht"),        @"฿",    @0, @"#,###.##" ],
          @[ @"AUD", RichLocaleString(@"Australian Dollar"),    @"$",    @0, @"# ###.##" ],
          @[ @"NZD", RichLocaleString(@"New Zealand Dollar"),   @"$",    @0, @"#,###.##" ],
          @[ @"CHF", RichLocaleString(@"Swiss Franc"),          @"Fr",   @0, @"#'###.##" ],
          @[ @"SEK", RichLocaleString(@"Swedish Krona"),        @"kr",   @1, @"#.###,##" ],
          @[ @"CAD", RichLocaleString(@"Canadian Dollar"),      @"$",    @0, @"#,###.##" ],
          @[ @"INR", RichLocaleString(@"Indian Rupee"),         @"₹",    @0, @"#,##,###.##" ],
          @[ @"RUB", RichLocaleString(@"Russian Ruble"),        @"pyб.", @1, @"#.###,##" ],
          @[ @"SGD", RichLocaleString(@"Singapore Dollar"),     @"$",    @0, @"#,###.##" ],
          @[ @"MYR", RichLocaleString(@"Malaysian Ringgit"),    @"RM",   @0, @"#,###.##" ],
          @[ @"MXN", RichLocaleString(@"Mexican Peso"),         @"¢",    @0, @"#,###.##" ],
          @[ @"BRL", RichLocaleString(@"Brazilian Real"),       @"$",    @0, @"#,###.##" ],
          @[ @"PHP", RichLocaleString(@"Philippine Peso"),      @"₱",    @0, @"#,###.##" ],
          @[ @"CUP", RichLocaleString(@"Cuban Peso"),           @"$",    @0, @"#,###.##" ],
          @[ @"CZK", RichLocaleString(@"Czech Koruna"),         @"Kč",   @1, @"#.###,##" ],
          @[ @"BND", RichLocaleString(@"Brunei Dollar"),        @"$",    @0, @"#,###.##" ],
          @[ @"TRY", RichLocaleString(@"New Turkish Lira"),     @"₺",    @0, @"#,###.##" ],
          @[ @"VND", RichLocaleString(@"Vietnamese Dong"),      @"₫",    @1, @"#,###" ],
          @[ @"XAU", RichLocaleString(@"Gold Ounce"),           @"$",    @0, @"#,###" ],
          @[ @"XAG", RichLocaleString(@"Silver Ounce"),         @"$",    @0, @"#,###" ]];
    
    int result = [self loadCurrency];
    
    if (result > 0)
        return 0;
    if (result == -ERROR_CORE_DB_QUERY)
        return -ERROR_CORE_DB_QUERY;
    
    // TODO: [Currency] Sort by locale
    for (int i = 0; i < [defaultCurrencyTable count]; i++) {
        [self createCurrency:[[defaultCurrencyTable objectAtIndex:i] objectAtIndex:0]
                        name:[[defaultCurrencyTable objectAtIndex:i] objectAtIndex:1]
                      symbol:[[defaultCurrencyTable objectAtIndex:i] objectAtIndex:2]
                    position:[(NSNumber *)[[defaultCurrencyTable objectAtIndex:i] objectAtIndex:3] integerValue]
                      format:[[defaultCurrencyTable objectAtIndex:i] objectAtIndex:4]];
    }
    
    return 0;
}

- (int)loadCurrency
{
    SQLMResultSet *rs;
    NSUInteger dataCount;
    
    if (currencies == nil) return -ERROR_CORE_UNINITIALIZED;
    
    rs = [sqliteManager selectColumns:@"*" fromTable:RichTableCurrency where:nil];
    
    if (rs == nil) return -ERROR_CORE_DB_QUERY;
    
    if ([rs dataCount] == 0) return 0;
    
    while ([rs next]) {
        RichCurrency *curr = [[RichCurrency alloc] init];
        curr.code = [[rs stringForColumnIndex:0] copy];
        curr.name = [[rs stringForColumnIndex:1] copy];
        curr.symbol = [[rs stringForColumnIndex:2] copy];
        curr.position = [rs intForColumnIndex:3];
        curr.format = [[rs stringForColumnIndex:4] copy];
        
        [currencies setObject:curr forKey:curr.code];
        
        [curr release];
    }
    
    dataCount = [rs dataCount];
    [rs release];
    
    return (int)dataCount;
}

- (int64_t)createCurrency:(NSString *)code name:(NSString *)name symbol:(NSString *)symbol position:(NSUInteger)position format:(NSString *)format
{
    if (code == nil || name == nil)
        return -ERROR_CORE_INVALID_PARM;
    
    int64_t rowId = [sqliteManager insertColumns:@"code, name, symbol, position, format"
                                      withValues:[NSString stringWithFormat:@"'%@', '%@', '%@', %ld, '%@'", code, name, symbol, position, format]
                                       intoTable:RichTableCurrency];
    
    if (rowId < 0) return rowId;
    
    RichCurrency *curr = [[RichCurrency alloc] init];
    curr.code = [code copy];
    curr.name = [name copy];
    curr.symbol = [symbol copy];
    curr.position = position;
    curr.format = [format copy];
    
    [currencies setObject:curr forKey:code];
    
    [curr release];
    
    return 0;
}

- (NSUInteger)currencyDecimalPosition:(NSString *)code
{
    RichCurrency *currency = [currencies objectForKey:code];
    NSUInteger decimalPosition = [currency.format length];
    NSRange range = [currency.format rangeOfString:@"."];
    
    if (decimalPosition == [@"#,###" length])
        return 0;
    
    if (range.location != NSNotFound)
        decimalPosition = range.location + 1;
    
    range = [currency.format rangeOfString:@","];
    if (range.location != NSNotFound)
        if (range.location + 1 > decimalPosition)
            decimalPosition = range.location + 1;
    
    return [currency.format length] - decimalPosition;
}

- (NSString *)currencyDecimalFormatString:(NSString *)code
{
    switch ([self currencyDecimalPosition:code]) {
        case 2:
            return @"%0.2f";

        case 3:
            return @"%0.3f";

        default:
            return @"%0.0f";
    }
}

- (NSString *)currencyFormatWithSign:(double)amount code:(NSString *)code
{
    RichCurrency *currency = [currencies objectForKey:code];
    NSString *amountString, *intString = @"";
    NSString *formatFloatPart, *formatIntPart;
    NSString *currChar, *separateChar = @"";
    int i, j, k, separateDigit = 0;
    int dividend = (int)amount;
    
    if (currency == nil)
        return @"";

    formatFloatPart = [self currencyDecimalFormatString:code];
    
    amountString = [NSString stringWithFormat:formatFloatPart, amount - (int)amount];
    amountString = [amountString substringWithRange:NSMakeRange(1, [amountString length] - 1)];
    
    if ([self currencyDecimalPosition:code] > 0)
        formatIntPart = [currency.format substringWithRange:
                         NSMakeRange(0, [currency.format length] - [self currencyDecimalPosition:code] - 1)];
    else
        formatIntPart = currency.format;

    for (i = (int)[formatIntPart length] - 1, j = 0; dividend >= 10 && i >= 0; i--) {
        currChar = [formatIntPart substringWithRange:NSMakeRange(i, 1)];
        if ([currChar isEqualToString:@"#"]) {
            dividend = (int)amount / (int)pow(10, j);
            intString = [NSString stringWithFormat:@"%d%@", dividend % 10, intString];
            ++j;
            ++separateDigit;
        }
        else {
            intString = [NSString stringWithFormat:@"%@%@", currChar, intString];
            separateChar = currChar;
            
            if (i > 1)
                separateDigit = 0;
            else if (i == 1)
                break;
        }
    }
    
    dividend = (int)amount / (int)pow(10, j);
    
    for (k = separateDigit; separateDigit > 0 && dividend > 0; dividend = (int)amount / (int)pow(10, j)) {
        if (k > 0) {
            intString = [NSString stringWithFormat:@"%d%@", dividend % 10, intString];
            ++j;
            --k;
        }
        else {
            intString = [NSString stringWithFormat:@"%@%@", separateChar, intString];
            k = separateDigit;
        }
    }
    
    if ([intString isEqual:@""])
        intString = [NSString stringWithFormat:@"%d", dividend];
    
    amountString = [NSString stringWithFormat:@"%@%@", intString, amountString];
    
    if (currency.position == 0)
        amountString = [NSString stringWithFormat:@"%@ %@", currency.symbol, amountString];
    else
        amountString = [NSString stringWithFormat:@"%@ %@", amountString, currency.symbol];
    
    return amountString;
}

#pragma mark - Account

- (RichAccount *)accounts:(NSMutableArray *)accounts findAccountByID:(NSInteger)accountID
{
    if ([accounts count] == 0)
        [self accounts:accounts loadWithType:AccountTypeAll];
    
    for (RichAccount *a in accounts) {
        if (a.accountId == accountID)
            return a;
    }
    
    return nil;
}

- (RichAccount *)accounts:(NSMutableArray *)accounts findAccountByName:(NSString *)accountName
{
    if ([accounts count] == 0)
        [self accounts:accounts loadWithType:AccountTypeAll];
    
    for (RichAccount *a in accounts) {
        if ([a.name isEqualToString:accountName])
            return a;
    }
    
    return nil;
}

- (int)accounts:(NSMutableArray *)accounts loadWithID:(NSInteger)accountID
{
    NSString *condition = nil;
    
    condition = [NSString stringWithFormat:@"id_pk = %ld", accountID];
    
    return [self accounts:accounts loadWithCondition:condition];
}

- (int)accounts:(NSMutableArray *)accounts loadWithType:(AccountType)type
{
    NSString *condition = nil;
    
    if (type != AccountTypeAll)
        condition = [NSString stringWithFormat:@"type = %d", type];
    
    return [self accounts:accounts loadWithCondition:condition];
}

- (int)accounts:(NSMutableArray *)accounts loadWithCondition:(NSString *)condition
{
    SQLMResultSet *rs;
    RichAccount *account;
    NSUInteger dataCount;
    
    rs = [sqliteManager selectColumns:@"*" fromTable:RichTableAccounts where:condition orderBy:@"id_pk" descending:NO];
    
    if (rs == nil) return -ERROR_CORE_DB_QUERY;
    
    dataCount = [rs dataCount];
    
    if (dataCount == 0) {
        [rs release];
        goto exit;
    }
    
    [accounts removeAllObjects];
    
    while ([rs next]) {
        account = [[RichAccount alloc] init];
        account.accountId      = [rs longForColumnIndex:0];
        account.name           = [rs stringForColumnIndex:1];
        account.type           = [rs intForColumnIndex:2];
        account.currencyCode   = [rs stringForColumnIndex:3];
        account.initialBalance = [rs doubleForColumnIndex:4];
        account.balance        = [rs doubleForColumnIndex:5];
        account.balanceDate    = [self stringToDate:[rs stringForColumnIndex:6] format:nil];
        account.creditCardAPR  = [rs doubleForColumnIndex:7];
        account.property       = [rs intForColumnIndex:8];
        account.description    = [rs stringForColumnIndex:9];
        
        [accounts addObject:account];
        
        [account release];
    }
    
    [rs release];
    
exit:
    [[NSNotificationCenter defaultCenter] postNotificationName:RichCoreNotificationReloadAccount object:nil];
    
    return (int)dataCount;
}

- (void)accountsCalculateBalance:(NSMutableArray *)accounts
{
    NSInteger totalExpend, totalIncome;
    SQLMResultSet *rs;
    
    for (RichAccount *account in accounts) {
        NSString *condition = [NSString stringWithFormat:@"from_account_id_fk = %ld", account.accountId];
        rs = [sqliteManager selectColumns:@"sum(amount) + sum(commission)" fromTable:RichTableGeneralTransactions where:condition];
        while ([rs next])
            totalExpend = [rs doubleForColumnIndex:0];
        
        condition = [NSString stringWithFormat:@"to_account_id_fk = %ld", account.accountId];
        rs = [sqliteManager selectColumns:@"sum(amount)" fromTable:RichTableGeneralTransactions where:condition];
        while ([rs next])
            totalIncome = [rs doubleForColumnIndex:0];
        
        // TODO: [Core] Calculate balance by saved balance and balanceDate to reduce sqlite query
        
        account.balance = account.initialBalance + totalIncome - totalExpend;
        account.balanceDate = [NSDate date];
        
        [[RichDelegate core] accounts:accounts update:account];
    }
}

- (BOOL)__isAccountCorrect:(RichAccount *)account
{
    if (account == nil ||
        account.name == nil ||
        account.type == AccountTypeNotSupport ||
        account.type >= AccountTypeInvisibleEnd ||
        [[self currencies] objectForKey:account.currencyCode] == nil)
        return NO;
    else
        return YES;
}

- (int64_t)accounts:(NSMutableArray *)accounts create:(RichAccount *)account
{
    if ([self __isAccountCorrect:account] == NO)
        return -ERROR_CORE_INVALID_PARM;   /* Invalid account info */
    
    if ([self accounts:accounts findAccountByName:account.name])
        return -ERROR_CORE_OCCUPIED;
    
    int64_t rowId = [sqliteManager insertColumns:@"name, type, currency_code_fk, initial_balance, balance, balance_date, credit_card_apr, property, description"
                                      withValues:[NSString stringWithFormat:@"\"%@\", %d, \"%@\", %f, %f, \"%@\", %f, %d, \"%@\"",
                                                 account.name,
                                                 account.type,
                                                 account.currencyCode,
                                                 account.initialBalance,
                                                 account.balance,
                                                 account.balanceDate,
                                                 account.creditCardAPR,
                                                 account.property,
                                                 account.description ? account.description : @""]
                                       intoTable:RichTableAccounts];
    
    if (rowId < 0) return rowId;
    
    account.accountId = rowId;
    [accounts addObject:account];
    
    return account.accountId;
}

- (int)accounts:(NSMutableArray *)accounts update:(RichAccount *)account
{
    RichAccount *accountToUpdate = nil;
    
    if ([self __isAccountCorrect:account] == NO)
        return -ERROR_CORE_INVALID_ACCOUNT;
    
    if ((accountToUpdate = [self accounts:accounts findAccountByID:account.accountId]) == nil)
        return -ERROR_CORE_ID_NOT_FOUND;   /* Account ID not found */
    
    int result = [sqliteManager updateTable:RichTableAccounts
                                 setColumns:[NSString stringWithFormat:
                                             @"name             = \"%@\", "
                                             @"type             = %d, "
                                             @"currency_code_fk = \"%@\", "
                                             @"initial_balance  = %f, "
                                             @"balance          = %f, "
                                             @"balance_date     = \"%@\", "
                                             @"credit_card_apr  = %f, "
                                             @"property         = %d, "
                                             @"description      = \"%@\"",
                                             account.name,
                                             account.type,
                                             account.currencyCode,
                                             account.initialBalance,
                                             account.balance,
                                             account.balanceDate,
                                             account.creditCardAPR,
                                             account.property,
                                             account.description ? account.description : @""]
                                      where:[NSString stringWithFormat:@"id_pk = %ld", account.accountId]];
    
    if (result < 0) return result;
    
    /* Update account-to-update if needed */
    if (accountToUpdate != account) {
        [accountToUpdate setName:account.name];
        [accountToUpdate setType:account.type];
        [accountToUpdate setCurrencyCode:account.currencyCode];
        [accountToUpdate setInitialBalance:account.initialBalance];
        [accountToUpdate setBalance:account.balance];
        [accountToUpdate setBalanceDate:account.balanceDate];
        [accountToUpdate setCreditCardAPR:account.creditCardAPR];
        [accountToUpdate setProperty:account.property];
        [accountToUpdate setDescription:account.description];
    }
    
    return RICH_OK;
}

- (int)accounts:(NSMutableArray *)accounts delete:(NSInteger)accountID
{
    for (RichAccount *a in accounts) {
        if (a.accountId == accountID) {
            [accounts removeObject:a];
            
            int result = [sqliteManager deleteFromTable:RichTableAccounts
                                                  where:[NSString stringWithFormat:@"id_pk = %ld", accountID]];
            
            return result;
        }
    }
    
    return -ERROR_CORE_ID_NOT_FOUND;   /* Account ID not found */;
}

- (NSString *)accountTypeToString:(AccountType)accountType
{
    if (accountType == AccountTypeCash)
        return RichLocaleString(@"Cash");
    else if (accountType == AccountTypeDepositSavings)
        return RichLocaleString(@"Saving Account");
    else if (accountType == AccountTypeDepositTimeDeposit)
        return RichLocaleString(@"Time Deposit Account");
    else if (accountType == AccountTypeDepositChecking)
        return nil; // RichLocaleString(@"Checking Account")
    else if (accountType == AccountTypeDepositMoneyMarket)
        return nil; // RichLocaleString(@"Money Market Account")
    else if (accountType == AccountTypeCreditCards)
        return RichLocaleString(@"Credit Card");
    else if (accountType == AccountTypeInsurance)
        return RichLocaleString(@"Insurance");
    else if (accountType == AccountTypeInvestmentStocks)
        return RichLocaleString(@"Stock");
    else if (accountType == AccountTypeInvestmentFunds)
        return RichLocaleString(@"Funds");
    else if (accountType == AccountTypeInvestmentFutures)
        return nil; // RichLocaleString(@"Futures")
    else if (accountType == AccountTypeInvestmentOptions)
        return nil; // RichLocaleString(@"Options")
    else if (accountType == AccountTypeStoredValueCards)
        return RichLocaleString(@"Stored Value Card");
    else
        return nil;
}

- (NSInteger)accountTypeFromString:(NSString *)accountType
{
    if ([accountType isEqualToString:RichLocaleString(@"Cash")])
        return AccountTypeCash;
    else if ([accountType isEqualToString:RichLocaleString(@"Saving Account")])
        return AccountTypeDepositSavings;
    else if ([accountType isEqualToString:RichLocaleString(@"Time Deposit Account")])
        return AccountTypeDepositTimeDeposit;
    else if ([accountType isEqualToString:RichLocaleString(@"Checking Account")])
        return AccountTypeDepositChecking;
    else if ([accountType isEqualToString:RichLocaleString(@"Money Market Account")])
        return AccountTypeDepositMoneyMarket;
    else if ([accountType isEqualToString:RichLocaleString(@"Credit Card")])
        return AccountTypeCreditCards;
    else if ([accountType isEqualToString:RichLocaleString(@"Insurance")])
        return AccountTypeInsurance;
    else if ([accountType isEqualToString:RichLocaleString(@"Stock")])
        return AccountTypeInvestmentStocks;
    else if ([accountType isEqualToString:RichLocaleString(@"Funds")])
        return AccountTypeInvestmentFunds;
    else if ([accountType isEqualToString:RichLocaleString(@"Futures")])
        return AccountTypeInvestmentFutures;
    else if ([accountType isEqualToString:RichLocaleString(@"Options")])
        return AccountTypeInvestmentOptions;
    else if ([accountType isEqualToString:RichLocaleString(@"Stored Value Card")])
        return AccountTypeStoredValueCards;
    else
        return AccountTypeNotSupport;
}

- (NSString *)accountPropertyToString:(AccountProperty)proterty
{
    switch (proterty) {
        case AccountPropertyDefault:
            return RichLocaleString(@"Default");
        case AccountPropertyNoStatistic:
            return RichLocaleString(@"No Statistic");
        case AccountPropertyCreditCardAutoPay:
            return RichLocaleString(@"Auto-pay Credit Card");
        case AccountPropertyStockSettlement:
            return RichLocaleString(@"Stock Settlement Account");
        
        default:
            return nil;
    }
}

- (NSInteger)accountPropertyFromString:(NSString *)property
{
    if ([property isEqualToString:RichLocaleString(@"Default")])
        return AccountPropertyDefault;
    else if ([property isEqualToString:RichLocaleString(@"No Statistic")])
        return AccountPropertyNoStatistic;
    else if ([property isEqualToString:RichLocaleString(@"Auto-pay Credit Card")])
        return AccountPropertyCreditCardAutoPay;
    else if ([property isEqualToString:RichLocaleString(@"Stock Settlement Account")])
        return AccountPropertyStockSettlement;
    else
        return -1;
}

#pragma mark - Category

- (BOOL)isCategoryNameUnique:(RichCategoryCode)code name:(NSString *)name
{
    NSUInteger mskTrue, mskFalse;
    
    if (code & 0xff000000) {
        // Level 3 mask
        mskTrue = 0xff000000;
        mskFalse = 0x00000000;
    }
    else if (code & 0x00ff0000) {
        // Level 2 mask
        mskTrue = 0x00ff0000;
        mskFalse = 0xff000000;
    }
    else if (code & 0x0000ff00) {
        // Level 1 mask
        mskTrue = 0x0000ff00;
        mskFalse = 0xffff0000;
    }
    else {
        // Level 0 mask
        mskTrue = 0x000000ff;
        mskFalse = 0xffffff00;
    }
    
    if ([category count] == 0)
        [self loadCategories];
    
    for (NSNumber *key in [category keyEnumerator]) {
        if (([key intValue] & mskTrue) &&
            !([key intValue] & mskFalse) &&
            [(NSString *)[category objectForKey:key] isEqualToString:name])
            return NO;
    }
    
    return YES;
}

- (NSString *)findCategoryByCode:(RichCategoryCode)code
{
    if ([category count] == 0)
        [self loadCategories];
    
    for (NSNumber *key in [category keyEnumerator]) {
        if ([key intValue] == code)
            return (NSString *)[category objectForKey:key];
    }
    
    return nil;
}

- (NSInteger)levelOfCategoryCode:(RichCategoryCode)code
{
    NSUInteger mskTrue[4] = { 0x000000ff, 0x0000ff00, 0x00ff0000, 0xff000000 };
    NSUInteger mskFalse[4] = { 0xffffff00, 0xffff0000, 0xff000000, 0x00000000 };
    NSUInteger i;
    
    for (i = 0; i < 4; i++)
        if ((code & mskTrue[i]) && !(code & mskFalse[i]))
            return i;
    
    return -1;
}

- (NSInteger)rootCategoryOf:(RichCategoryCode)code
{
    switch (code & RootCategoryAll) {
        case RootCategoryExpend:
        case RootCategoryIncome:
        case RootCategoryTransfer:
        case RootCategoryAll:
            return code & RootCategoryAll;
        
        default:
            return RootCategoryUnknown;
    }
}

- (NSInteger)parentCodeOf:(RichCategoryCode)code atLevel:(NSUInteger)level
{
    NSUInteger mskParent[4] = { 0x00000000, 0x000000ff, 0x0000ffff, 0x00ffffff };
    
    if (level >= 4)
        return -1;
    
    return code & mskParent[level];
}

- (NSInteger)nextCategoryIdAtLevel:(NSUInteger)level parentCode:(RichCategoryCode)parentCode
{
    NSUInteger mskParent[4] = { 0x00000000, 0x000000ff, 0x0000ffff, 0x00ffffff };
    NSUInteger mskTrue[4] = { 0x000000ff, 0x0000ff00, 0x00ff0000, 0xff000000 };
    NSUInteger mskFalse[4] = { 0xffffff00, 0xffff0000, 0xff000000, 0x00000000 };
    NSInteger last[4] = { 0, 0, 0, 0 };
    NSInteger currId;
    
    if (level >= 4)
        return -1;
    
    for (NSNumber *code in [[self category] allKeys]) {
        currId = ([code intValue] & mskTrue[level]) >> (level * 8);
        if (([code intValue] & mskTrue[level]) &&
            !([code intValue] & mskFalse[level]) &&
            (([code intValue] & mskParent[level]) == parentCode))
            if (currId > last[level])
                last[level] = currId;
    }
    
    return last[level] + 1;
}

- (NSMutableDictionary *)categoriesAtLevel:(NSUInteger)level parentCode:(RichCategoryCode)parentCode
{
    NSUInteger mskParent[4] = { 0x00000000, 0x000000ff, 0x0000ffff, 0x00ffffff };
    NSUInteger mskTrue[4] = { 0x000000ff, 0x0000ff00, 0x00ff0000, 0xff000000 };
    NSUInteger mskFalse[4] = { 0xffffff00, 0xffff0000, 0xff000000, 0x00000000 };
    NSMutableDictionary *categories = [[[NSMutableDictionary alloc] init] autorelease];
    
    if (level >= 4)
        return nil;
    
    for (NSNumber *code in [[self category] allKeys]) {
        if (([code intValue] & mskTrue[level]) &&
            !([code intValue] & mskFalse[level]) &&
            (([code intValue] & mskParent[level]) == parentCode))
            [categories setObject:[[self category] objectForKey:code] forKey:code];
    }
    
    return categories;
}

- (int)loadCategories
{
    SQLMResultSet *rs;
    NSUInteger dataCount;
    
    if (category == nil) return -ERROR_CORE_UNINITIALIZED;
    
    [category removeAllObjects];
    
    rs = [sqliteManager selectColumns:@"code, name" fromTable:RichTableCategory where:nil];
    
    if (rs == nil) return -ERROR_CORE_DB_QUERY;
    
    dataCount = [rs dataCount];
    
    if (dataCount == 0) {
        [rs release];
        goto exit;
    }
    
    while ([rs next]) {
        [category setObject:[rs stringForColumnIndex:1]
                     forKey:[NSNumber numberWithInt:[rs intForColumnIndex:0]]];
    }
    
    [rs release];
    
exit:
    [[NSNotificationCenter defaultCenter] postNotificationName:RichCoreNotificationReloadCategory object:nil];
    
    return (int)dataCount;
}

- (int64_t)createCategory:(RichCategoryCode)categoryCode name:(NSString *)categoryName
{
    if (categoryCode == 0 || categoryName == nil)
        return -ERROR_CORE_INVALID_PARM;
    
    // Check uniqueness of category name at the same hierarchy
    if ([self isCategoryNameUnique:categoryCode name:categoryName] == NO)
        return -ERROR_CORE_OCCUPIED;
    
    int64_t rowId = [sqliteManager insertColumns:@"code, name"
                                      withValues:[NSString stringWithFormat:@"%ld, \"%@\"", categoryCode, categoryName]
                                       intoTable:RichTableCategory];
    
    if (rowId < 0) return rowId;
    
    [category setObject:categoryName forKey:[NSNumber numberWithUnsignedInteger:categoryCode]];
    
    return RICH_OK;
}

- (int)updateCategory:(RichCategoryCode)categoryCode name:(NSString *)categoryName
{
    if (categoryCode == 0 || categoryName == nil)
        return -ERROR_CORE_INVALID_PARM;
    
    // Check uniqueness of category name at the same hierarchy
    if ([self isCategoryNameUnique:categoryCode name:categoryName] == NO)
        return -ERROR_CORE_OCCUPIED;
    
    for (NSNumber *key in [category keyEnumerator]) {
        if ([key integerValue] == categoryCode) {
            int result = [sqliteManager updateTable:RichTableCategory
                                         setColumns:[NSString stringWithFormat:@"name = \"%@\"", categoryName]
                                              where:[NSString stringWithFormat:@"code = %ld", categoryCode]];
            
            if (result < 0) return result;
            
            [category setObject:categoryName forKey:key];
            return RICH_OK;
        }
    }
    
    return -ERROR_CORE_ID_NOT_FOUND;
}

- (int)deleteCategory:(RichCategoryCode)categoryCode
{
    NSUInteger level;
    NSUInteger mskChildren[4] = { 0x000000ff, 0x0000ffff, 0x00ffffff, 0xffffffff };
    
    if ([category objectForKey:[NSNumber numberWithUnsignedInteger:categoryCode]] == nil)
        return -ERROR_CORE_ID_NOT_FOUND;   /* Category code not found */
    
    level = [self levelOfCategoryCode:categoryCode];
    
    int result = [sqliteManager deleteFromTable:RichTableCategory
                                          where:[NSString stringWithFormat:@"(code & %ld) = %ld", mskChildren[level], categoryCode]];
    
    if (result < 0) return result;
    
    [category removeObjectForKey:[NSNumber numberWithUnsignedInteger:categoryCode]];
    
    return RICH_OK;
}

#pragma mark - Transaction

- (double)calculateLoanPayment:(double)loan times:(NSUInteger)times annualRate:(double)rate
{
    double rate_per_month = rate / 12.0f;
    
    if (rate == 0.0f)
        return loan / (times - 1);
    else
        return (loan * rate_per_month) / (1.0f - 1.0f / powf(1.0f + rate_per_month, times - 1));
}

- (BOOL)__isTransactionCorrect:(id *)transaction type:(NSString *)type
{
    if (transaction == NULL) return NO;
    
    NSMutableArray *accounts = [[NSMutableArray alloc] init];
    [self accounts:accounts loadWithType:AccountTypeAll];
    
    if ([type isEqualToString:kTransactionTypeGeneral]) {
        RichTransactionGeneral *trans = (RichTransactionGeneral *)transaction;
        if (trans.date == nil)
            return NO;
        
        if ([self rootCategoryOf:trans.categoryCode] == RootCategoryUnknown)
            return NO;
        
        if ([self findCategoryByCode:trans.categoryCode] == nil)
            return NO;
        
        if (([self rootCategoryOf:trans.categoryCode] == RootCategoryExpend ||
             [self rootCategoryOf:trans.categoryCode] == RootCategoryTransfer) &&
            [self accounts:accounts findAccountByID:trans.accountFrom] == NO)
            return NO;
        
        if (([self rootCategoryOf:trans.categoryCode] == RootCategoryIncome ||
             [self rootCategoryOf:trans.categoryCode] == RootCategoryTransfer)
            && [self accounts:accounts findAccountByID:trans.accountTo] == NO)
            return NO;
        
        if ([self rootCategoryOf:trans.categoryCode] == RootCategoryExpend &&
            trans.accountTo != 0)
            return NO;
        
        if ([self rootCategoryOf:trans.categoryCode] == RootCategoryIncome &&
            trans.accountFrom != 0)
            return NO;
    }
    else if ([type isEqualToString:kTransactionTypeStock]) {
        RichTransactionStock *trans = (RichTransactionStock *)transaction;
        if (trans.stockCode == 0)
            return NO;
        
        if (trans.type >= StockTransactionTypeEnd)
            return NO;
        
        if (trans.date == nil)
            return NO;
        
        if ([self accounts:accounts findAccountByID:trans.accountStock] == NO)
            return NO;
        
        if ([self accounts:accounts findAccountByID:trans.accountSettlement] == NO)
            return NO;
    }
    else if ([type isEqualToString:kTransactionTypeFund]) {
        // TODO: [Fund]
    }
    else {
        return NO;
    }
    
    [accounts removeAllObjects];
    [accounts release];
    
    return YES;
}

- (id *)findTransactionIn:(NSMutableArray *)transactions byID:(int64_t)transID type:(NSString *)type
{
    if ([type isEqualToString:kTransactionTypeGeneral]) {
        for (RichTransactionGeneral *__trans in transactions) {
            if (__trans.transId == transID)
                return (id *)__trans;
        }
    }
    else if ([type isEqualToString:kTransactionTypeStock]) {
        for (RichTransactionStock *__trans in transactions) {
            if (__trans.transId == transID)
                return (id *)__trans;
        }
    }
    else if ([type isEqualToString:kTransactionTypeFund]) {
        for (RichTransactionFund *__trans in transactions) {
            if (__trans.transId == transID)
                return (id *)__trans;
        }
    }
    
    return nil;
}

- (NSString *)stringFromTransactionProperty:(NSUInteger)property
{
    switch (property) {
        case TransactionPropertyExpendNone:
        case TransactionPropertyIncomeNone:
        case TransactionPropertyTransferNone:
            return RichLocaleString(@"None");
        case TransactionPropertyExpendNeed:
            return RichLocaleString(@"Need");
        case TransactionPropertyExpendWant:
            return RichLocaleString(@"Want");
        case TransactionPropertyIncomeLabor:
            return RichLocaleString(@"Labor Income");
        case TransactionPropertyIncomeInterest:
            return RichLocaleString(@"Interest Income");
        default:
            return nil;
    }
}

- (NSString *)stringFromInstallmentPeriod:(NSUInteger)period
{
    switch (period) {
        case TransactionInstallmentPeriodMonthly:
            return RichLocaleString(@"Monthly");
        case TransactionInstallmentPeriodQuarterly:
            return RichLocaleString(@"Quarterly");
        case TransactionInstallmentPeriodSemiannual:
            return RichLocaleString(@"Semiannual");
        case TransactionInstallmentPeriodAnnual:
            return RichLocaleString(@"Annual");
        default:
            return nil;
    }
}

- (int)reloadTransactions
{
    if (_currTransType != nil && _currTransLimit > 0) {
        return [self loadTransactions:_currTransArray
                                 type:_currTransType
                                where:_currTransCondition
                         beginAtIndex:_currTransBegin
                                limit:_currTransLimit
                           descending:_currDescending];
    }
    
    return 0;
}

- (RichTransactionGeneral *)loadGeneralTransaction:(uint64_t)transId
{
    RichTransactionGeneral *transaction = NULL;
    SQLMResultSet *rs;
    NSUInteger dataCount;
    
    rs = [sqliteManager selectColumns:@"*" fromTable:RichTableGeneralTransactions where:[NSString stringWithFormat:@"id_pk = %lld", transId]];
    
    if (rs == nil) return NULL;
    
    dataCount = [rs dataCount];
    
    if (dataCount == 0) {
        [rs release];
        return NULL;
    }
    
    while ([rs next]) {
        transaction = [[RichTransactionGeneral alloc] init];
        transaction.transId                 = [rs longForColumnIndex:0];
        transaction.date                    = [self stringToDate:[rs stringForColumnIndex:1] format:nil];
        transaction.categoryCode            = [rs intForColumnIndex:2];
        transaction.accountFrom             = [rs intForColumnIndex:3];
        transaction.accountTo               = [rs intForColumnIndex:4];
        transaction.creditCardBillId        = [rs intForColumnIndex:5];
        transaction.installmentPaymentTimes = [rs intForColumnIndex:6];
        transaction.installmentDownPayment  = [rs doubleForColumnIndex:7];
        transaction.installmentRate         = [rs doubleForColumnIndex:8];
        transaction.installmentPeriod       = [rs intForColumnIndex:9];
        transaction.installmentMade         = [rs intForColumnIndex:10];
        transaction.installmentTransId      = [rs intForColumnIndex:11];
        transaction.amount                  = [rs doubleForColumnIndex:12];
        transaction.property                = [rs intForColumnIndex:13];
        transaction.commission              = [rs doubleForColumnIndex:14];
        transaction.numberOfReceipts        = [rs intForColumnIndex:15];
        transaction.receiptNo               = [rs stringForColumnIndex:16];
        transaction.comment                 = [rs stringForColumnIndex:17];
    }
    
    [rs release];
    return transaction;
}

- (int)loadGeneralTransactions:(NSMutableArray *)transactions category:(RootCategory)rootCategory where:(NSString *)conditions beginAtIndex:(int64_t)begin limit:(NSUInteger)limit descending:(BOOL)descending
{
    NSString *_condition;
    
    if ([self rootCategoryOf:rootCategory] == RootCategoryUnknown)
        return -ERROR_CORE_INVALID_CATEGORY;
    
    if (rootCategory == RootCategoryAll)
        _condition = conditions;
    else
        _condition = [NSString stringWithFormat:@"category_code_fk & 255 = %d and %@", rootCategory, conditions];
    
    return [self loadTransactions:transactions type:kTransactionTypeGeneral where:_condition beginAtIndex:begin limit:limit descending:descending];
}

- (int)loadTransactions:(NSMutableArray *)transactions type:(NSString *)type where:(NSString *)conditions beginAtIndex:(int64_t)begin limit:(NSUInteger)limit descending:(BOOL)descending
{
    SQLMResultSet *rs;
    NSUInteger dataCount;
    
    _currTransArray = transactions;
    _currTransType = type;
    _currTransCondition = [conditions copy];
    _currTransBegin = begin;
    _currTransLimit = limit;
    _currDescending = descending;
    
    if (transactions == nil) return -ERROR_CORE_UNINITIALIZED;
    
    if ([type isEqualToString:kTransactionTypeGeneral])
    {
        /*
         * General Transaction
         */
        [transactions removeAllObjects];
        
        rs = [sqliteManager selectColumns:@"*" fromTable:RichTableGeneralTransactions where:_currTransCondition
                                  orderBy:@"date" descending:descending
                                    limit:(limit > 0) ? [NSString stringWithFormat:@"%lld,%ld", begin, limit]: NULL];
        
        if (rs == nil) return -ERROR_CORE_DB_QUERY;
        
        dataCount = [rs dataCount];
        
        if (dataCount == 0) {
            [rs release];
            goto exit_general;
        }
        
        while ([rs next]) {
            RichTransactionGeneral *transaction = [[RichTransactionGeneral alloc] init];
            transaction.transId                 = [rs longForColumnIndex:0];
            transaction.date                    = [self stringToDate:[rs stringForColumnIndex:1] format:nil];
            transaction.categoryCode            = [rs intForColumnIndex:2];
            transaction.accountFrom             = [rs intForColumnIndex:3];
            transaction.accountTo               = [rs intForColumnIndex:4];
            transaction.creditCardBillId        = [rs intForColumnIndex:5];
            transaction.installmentPaymentTimes = [rs intForColumnIndex:6];
            transaction.installmentDownPayment  = [rs intForColumnIndex:7];
            transaction.installmentRate         = [rs doubleForColumnIndex:8];
            transaction.installmentPeriod       = [rs intForColumnIndex:9];
            transaction.installmentMade         = [rs intForColumnIndex:10];
            transaction.installmentTransId      = [rs intForColumnIndex:11];
            transaction.amount                  = [rs doubleForColumnIndex:12];
            transaction.property                = [rs intForColumnIndex:13];
            transaction.commission              = [rs doubleForColumnIndex:14];
            transaction.numberOfReceipts        = [rs intForColumnIndex:15];
            transaction.receiptNo               = [rs stringForColumnIndex:16];
            transaction.comment                 = [rs stringForColumnIndex:17];
            
            [transactions addObject:transaction];
            [transaction release];
        }
        
        [rs release];
        
exit_general:
        [[NSNotificationCenter defaultCenter] postNotificationName:RichCoreNotificationReloadTransaction object:nil];
        
        return (int)dataCount;
    }
    else if ([type isEqualToString:kTransactionTypeStock])
    {
        /*
         * Stock Transaction
         */
        [transactions removeAllObjects];
        
        rs = [sqliteManager selectColumns:@"*" fromTable:RichTableStockTransactions where:conditions
                                  orderBy:@"date" descending:YES limit:[NSString stringWithFormat:@"%lld,%ld", begin, limit]];
        
        if (rs == nil) return -ERROR_CORE_DB_QUERY;
        
        dataCount = [rs dataCount];
        
        if (dataCount == 0) {
            [rs release];
            goto exit_stock;
        }
        
        while ([rs next]) {
            RichTransactionStock *transaction = [[RichTransactionStock alloc] init];
            transaction.transId            = [rs longForColumnIndex:0];
            transaction.date               = [self stringToDate:[rs stringForColumnIndex:1] format:nil];
            transaction.closingDate        = [self stringToDate:[rs stringForColumnIndex:2] format:nil];
            transaction.type               = [rs intForColumnIndex:3];
            transaction.stockCode          = [rs intForColumnIndex:4];
            transaction.accountStock       = [rs intForColumnIndex:5];
            transaction.accountSettlement  = [rs intForColumnIndex:6];
            transaction.price              = [rs doubleForColumnIndex:7];
            transaction.downPaymentPercent = [rs intForColumnIndex:8];
            transaction.numberOfShare      = [rs intForColumnIndex:9];
            transaction.tradingTax         = [rs doubleForColumnIndex:10];
            transaction.commission         = [rs doubleForColumnIndex:11];
            transaction.interest           = [rs doubleForColumnIndex:12];
            transaction.amount             = [rs doubleForColumnIndex:13];
            transaction.comment            = [rs stringForColumnIndex:14];
            
            [transactions addObject:transaction];
            [transaction release];
        }
        
        [rs release];
        
exit_stock:
        [[NSNotificationCenter defaultCenter] postNotificationName:RichCoreNotificationReloadTransaction object:nil];
        
        return (int)dataCount;
    }
    else if ([type isEqualToString:kTransactionTypeFund])
    {
        /*
         * Fund Transaction
         */
        //NSMutableArray *_fundTransactions = [transactions objectForKey:kTransactionTypeFund];
        // TODO: [Fund]
    }
    else {
        return -ERROR_CORE_INVALID_TRANSACTION_TYPE;
    }
    
    return 0;
}

- (int64_t)createGeneralTransaction:(RichTransactionGeneral *)transaction
{
    int64_t rowId = 0;
    
    if ([self __isTransactionCorrect:(id *)transaction type:kTransactionTypeGeneral] == NO)
        return -ERROR_CORE_INVALID_TRANSACTION;
    
    if (transaction.installmentPaymentTimes > 1) {
        transaction.installmentMade = 1;
    }
    else {
        transaction.installmentMade = 0;
    }
    
    transaction.installmentTransId = 0;
    
    rowId = [self createTransaction:(id *)transaction type:kTransactionTypeGeneral];
    
    /* Create installment transactions if any */
    for (int i = 1; i < transaction.installmentPaymentTimes; i++) {
        RichTransactionGeneral *instTrans = [[RichTransactionGeneral alloc] init];
        
        NSDateComponents *dateComponents = [[NSDateComponents alloc] init];
        
        if (transaction.installmentPeriod == TransactionInstallmentPeriodMonthly)
            [dateComponents setMonth:i];
        else if (transaction.installmentPeriod == TransactionInstallmentPeriodQuarterly)
            [dateComponents setMonth:i * 3];
        else if (transaction.installmentPeriod == TransactionInstallmentPeriodSemiannual)
            [dateComponents setMonth:i * 6];
        else
            [dateComponents setYear:i];
        NSCalendar *calendar = [NSCalendar currentCalendar];
        NSDate *newDate = [calendar dateByAddingComponents:dateComponents toDate:transaction.date options:0];
        [dateComponents release];
        
        instTrans.date = newDate;
        instTrans.categoryCode = transaction.categoryCode;
        instTrans.accountFrom = transaction.accountFrom;
        instTrans.accountTo = transaction.accountTo;
        instTrans.creditCardBillId = transaction.creditCardBillId;
        instTrans.installmentPaymentTimes = transaction.installmentPaymentTimes;
        instTrans.installmentDownPayment = transaction.installmentDownPayment;
        instTrans.installmentRate = transaction.installmentRate;
        instTrans.installmentTransId = transaction.transId;
        instTrans.installmentMade = i + 1;
        instTrans.amount = [[RichDelegate core] calculateLoanPayment:transaction.amount - transaction.installmentDownPayment
                                                               times:transaction.installmentPaymentTimes
                                                          annualRate:transaction.installmentRate];
        instTrans.property = transaction.property;
        instTrans.commission = 0;
        instTrans.numberOfReceipts = 0;
        instTrans.receiptNo = nil;
        
        rowId = [self createTransaction:(id *)instTrans type:kTransactionTypeGeneral];
        [instTrans release];
        
        if (rowId < 0) return rowId;
    }
    
    return rowId;
}

- (int64_t)createTransaction:(id *)transaction type:(NSString *)type
{
    int64_t rowId = 0;
    
    if ([self __isTransactionCorrect:transaction type:type] == NO)
        return -ERROR_CORE_INVALID_TRANSACTION;
    
    if ([type isEqualToString:kTransactionTypeGeneral]) {
        RichTransactionGeneral *trans = (RichTransactionGeneral *)transaction;
        
        rowId = [sqliteManager insertColumns:@"date, "
                                             @"category_code_fk, "
                                             @"from_account_id_fk, "
                                             @"to_account_id_fk, "
                                             @"credit_card_bill_id_fk, "
                                             @"installment_payment_times, "
                                             @"installment_down_payment, "
                                             @"installment_interest_rate, "
                                             @"installment_period, "
                                             @"installment_made, "
                                             @"installment_trans_id, "
                                             @"amount, "
                                             @"property, "
                                             @"commission, "
                                             @"number_of_receipts, "
                                             @"receipt_no, "
                                             @"comment"
                                  withValues:[NSString stringWithFormat:@"\"%@\", %ld, %ld, %ld, %ld, %ld, %f, %f, %ld, %ld, %lld, %f, %ld, %f, %ld, \"%@\", \"%@\"",
                                             [self stringFromDate:trans.date format:nil],
                                             trans.categoryCode,
                                             trans.accountFrom,
                                             trans.accountTo,
                                             trans.creditCardBillId,
                                             trans.installmentPaymentTimes,
                                             trans.installmentDownPayment,
                                             trans.installmentRate,
                                             trans.installmentPeriod,
                                             trans.installmentMade,
                                             trans.installmentTransId,
                                             trans.amount,
                                             trans.property,
                                             trans.commission,
                                             trans.numberOfReceipts,
                                             trans.receiptNo ? trans.receiptNo : @"",
                                             trans.comment ? trans.comment : @""]
                                   intoTable:RichTableGeneralTransactions];
        
        if (rowId < 0) return rowId;
        
        trans.transId = rowId;
    }
    else if ([type isEqualToString:kTransactionTypeStock]) {
        RichTransactionStock *trans = (RichTransactionStock *)transaction;
        
        rowId = [sqliteManager insertColumns:@"date, "
                                             @"closing_date, "
                                             @"type, "
                                             @"stock_code, "
                                             @"stock_account_id_fk, "
                                             @"settlement_account_id_fk, "
                                             @"price, "
                                             @"down_payment_percent, "
                                             @"number_of_share, "
                                             @"trading_tax, "
                                             @"commission, "
                                             @"interest, "
                                             @"amount, "
                                             @"comment"
                                  withValues:[NSString stringWithFormat:@"\"%@\", \"%@\", %d, \"%@\", %ld, %ld, %f, %ld, %ld, %f, %f, %f, %f, \"%@\"",
                                              [self stringFromDate:trans.date format:nil],
                                              [self stringFromDate:trans.closingDate format:nil],
                                              trans.type,
                                              trans.stockCode,
                                              trans.accountStock,
                                              trans.accountSettlement,
                                              trans.price,
                                              trans.downPaymentPercent,
                                              trans.numberOfShare,
                                              trans.tradingTax,
                                              trans.commission,
                                              trans.interest,
                                              trans.amount,
                                              trans.comment ? trans.comment : @""]
                                   intoTable:RichTableStockTransactions];
        
        if (rowId < 0) return rowId;
        
        trans.transId = rowId;
    }
    else if ([type isEqualToString:kTransactionTypeFund]) {
        // TODO: [Fund]
    }
    else {
        return -ERROR_CORE_INVALID_TRANSACTION_TYPE;
    }
    
#if 0
    if ([_currTransType isEqualToString:type]) {
        [self reloadTransactions];
    }
#endif
    
    return rowId;
}

- (int)updateGeneralTransaction:(RichTransactionGeneral *)transaction
{
    int result;
    
    if ([self __isTransactionCorrect:(id *)transaction type:kTransactionTypeGeneral] == NO)
        return -ERROR_CORE_INVALID_TRANSACTION;

#if 0
    RichTransactionGeneral *oldTrans = (RichTransactionGeneral *)
        [self findTransactionIn:_currTransArray byID:transaction.transId type:kTransactionTypeGeneral]; //FIXME: use _currTransArray?
    
    if (oldTrans == nil) {
        /* Account ID not found, probably not loaded into array */
        return -ERROR_CORE_ID_NOT_FOUND;
    }
#endif

    /* Remove related installment transactions */
    if (transaction.installmentTransId == 0) {
        [self deleteTransactionWithCondition:[NSString stringWithFormat:@"installment_trans_id = %lld", transaction.transId] type:kTransactionTypeGeneral];
        [self deleteTransaction:transaction.transId type:kTransactionTypeGeneral];
    }
    else {
        [self deleteTransactionWithCondition:[NSString stringWithFormat:@"installment_trans_id = %lld", transaction.installmentTransId] type:kTransactionTypeGeneral];
        [self deleteTransaction:transaction.installmentTransId type:kTransactionTypeGeneral];
    }
    
    if (transaction.installmentPaymentTimes > 0) {
        result = (int)[self createGeneralTransaction:transaction];
    }
    else {
        result = [self updateTransaction:(id *)transaction type:kTransactionTypeGeneral];
    }
    
    return result;
}

- (int)updateTransaction:(id *)transaction type:(NSString *)type
{
    if ([self __isTransactionCorrect:transaction type:type] == NO)
        return -ERROR_CORE_INVALID_TRANSACTION;
    
    if ([type isEqualToString:kTransactionTypeGeneral]) {
        RichTransactionGeneral *trans = (RichTransactionGeneral *)transaction;
        int result;
        
#if 0
        RichTransactionGeneral *oldTrans = (RichTransactionGeneral *)
            [self findTransactionIn:_currTransArray byID:trans.transId type:type]; //FIXME: use _currTransArray?
        
        if (oldTrans == nil) {
            /* Account ID not found, probably not loaded into array */
            return -ERROR_CORE_ID_NOT_FOUND;
        }
#endif
        result = [sqliteManager updateTable:RichTableGeneralTransactions
                                 setColumns:[NSString stringWithFormat:
                                             @"date                      = \"%@\", "
                                             @"category_code_fk          = %ld, "
                                             @"from_account_id_fk        = %ld, "
                                             @"to_account_id_fk          = %ld, "
                                             @"credit_card_bill_id_fk    = %ld, "
                                             @"installment_payment_times = %ld, "
                                             @"installment_down_payment  = %f, "
                                             @"installment_interest_rate = %f, "
                                             @"installment_period        = %ld, "
                                             @"installment_made          = %ld, "
                                             @"installment_trans_id      = %lld,"
                                             @"amount                    = %f, "
                                             @"property                  = %ld, "
                                             @"commission                = %f, "
                                             @"number_of_receipts        = %ld, "
                                             @"receipt_no                = \"%@\", "
                                             @"comment                   = \"%@\"",
                                             [self stringFromDate:trans.date format:nil],
                                             trans.categoryCode,
                                             trans.accountFrom,
                                             trans.accountTo,
                                             trans.creditCardBillId,
                                             trans.installmentPaymentTimes,
                                             trans.installmentDownPayment,
                                             trans.installmentRate,
                                             trans.installmentPeriod,
                                             trans.installmentMade,
                                             trans.installmentTransId,
                                             trans.amount,
                                             trans.property,
                                             trans.commission,
                                             trans.numberOfReceipts,
                                             trans.receiptNo ? trans.receiptNo : @"",
                                             trans.comment ? trans.comment : @""]
                                      where:[NSString stringWithFormat:@"id_pk = %lld", trans.transId]];
        
        if (result < 0) return result;
    }
    else if ([type isEqualToString:kTransactionTypeStock]) {
        RichTransactionStock *trans = (RichTransactionStock *)transaction;
        
        if ([self findTransactionIn:_currTransArray byID:trans.transId type:type] == nil) { //FIXME: use _currTransArray?
            /* Account ID not found, probably not loaded into array */
            return -ERROR_CORE_ID_NOT_FOUND;
        }
        
        int result = [sqliteManager updateTable:RichTableStockTransactions
                                     setColumns:[NSString stringWithFormat:
                                                 @"date                     = \"%@\", "
                                                 @"closing_date             = \"%@\", "
                                                 @"type                     = %d, "
                                                 @"stock_code               = \"%@\", "
                                                 @"stock_account_id_fk      = %ld, "
                                                 @"settlement_account_id_fk = %ld, "
                                                 @"price                    = %f, "
                                                 @"down_payment_percent     = %ld, "
                                                 @"number_of_share          = %ld, "
                                                 @"trading_tax              = %f, "
                                                 @"commission               = %f, "
                                                 @"interest                 = %f, "
                                                 @"amount                   = %f, "
                                                 @"comment                  = \"%@\"",
                                                 [self stringFromDate:trans.date format:nil],
                                                 [self stringFromDate:trans.closingDate format:nil],
                                                 trans.type,
                                                 trans.stockCode,
                                                 trans.accountStock,
                                                 trans.accountSettlement,
                                                 trans.price,
                                                 trans.downPaymentPercent,
                                                 trans.numberOfShare,
                                                 trans.tradingTax,
                                                 trans.commission,
                                                 trans.interest,
                                                 trans.amount,
                                                 trans.comment ? trans.comment : @""]
                                          where:[NSString stringWithFormat:@"id_pk = %lld", trans.transId]];
        
        if (result < 0) return result;
    }
    else if ([type isEqualToString:kTransactionTypeFund]) {
        // TODO: [Fund]
    }
    else {
        return -ERROR_CORE_INVALID_TRANSACTION_TYPE;
    }
    
#if 0
    if ([_currTransType isEqualToString:type]) {
        [self reloadTransactions];
    }
#endif
    
    return RICH_OK;
}

- (int)deleteTransaction:(int64_t)transactionID type:(NSString *)type
{
#if 0
    if ([self findTransactionIn:_currTransArray byID:transactionID type:type] == nil) { //FIXME: use _currTransArray?
        /* Account ID not found, probably not loaded into array */
        return -ERROR_CORE_ID_NOT_FOUND;
    }
#endif
    
    if ([type isEqualToString:kTransactionTypeGeneral]) {
        int result = [sqliteManager deleteFromTable:RichTableGeneralTransactions
                                              where:[NSString stringWithFormat:@"id_pk = %lld", transactionID]];
        
        if (result < 0) return result;
    }
    else if ([type isEqualToString:kTransactionTypeStock]) {
        int result = [sqliteManager deleteFromTable:RichTableStockTransactions
                                              where:[NSString stringWithFormat:@"id_pk = %lld", transactionID]];
        
        if (result < 0) return result;
    }
    else if ([type isEqualToString:kTransactionTypeFund]) {
        int result = [sqliteManager deleteFromTable:RichTableFundTransactions
                                              where:[NSString stringWithFormat:@"id_pk = %lld", transactionID]];
        
        if (result < 0) return result;
    }
    else {
        return -ERROR_CORE_INVALID_TRANSACTION_TYPE;
    }
    
#if 0
    if ([_currTransType isEqualToString:type]) {
        [self reloadTransactions];
    }
#endif
    
    return RICH_OK;
}

- (int)deleteTransactionWithCondition:(NSString *)condition type:(NSString *)type
{
    if ([type isEqualToString:kTransactionTypeGeneral]) {
        int result = [sqliteManager deleteFromTable:RichTableGeneralTransactions
                                              where:condition];
        
        if (result < 0) return result;
    }
    else if ([type isEqualToString:kTransactionTypeStock]) {
        int result = [sqliteManager deleteFromTable:RichTableStockTransactions
                                              where:condition];
        
        if (result < 0) return result;
    }
    else if ([type isEqualToString:kTransactionTypeFund]) {
        int result = [sqliteManager deleteFromTable:RichTableFundTransactions
                                              where:condition];
        
        if (result < 0) return result;
    }
    else {
        return -ERROR_CORE_INVALID_TRANSACTION_TYPE;
    }
    
#if 0
    if ([_currTransType isEqualToString:type]) {
        [self reloadTransactions];
    }
#endif
    
    return RICH_OK;
}

- (NSUInteger)totalAmountOfTransactions:(NSMutableArray *)transArray
{
    NSUInteger __amount = 0;
    
    for (RichTransactionGeneral *__trans in transArray) {
        if ([[RichDelegate core] rootCategoryOf:__trans.categoryCode] == RootCategoryExpend)
            __amount += __trans.amount;
        else if ([[RichDelegate core] rootCategoryOf:__trans.categoryCode] == RootCategoryIncome)
            __amount -= __trans.amount;
    }
    
    return __amount;
}

- (NSUInteger)totalAmountOfPayments:(NSMutableArray *)paymentArray
{
    NSUInteger __amount = 0;
    
    for (RichTransactionGeneral *__payment in paymentArray)
        if ([[RichDelegate core] rootCategoryOf:__payment.categoryCode] == RootCategoryTransfer)
            __amount += __payment.amount;
    
    return __amount;
}

#pragma mark - Credit Card Bills

- (void)creditCardBill:(RichCreditCardBill *)bill loadAssociatedTransactionsToArray:(NSMutableArray *)transArray database:(id *)database
{
    if (transArray == nil)
        return;
    
    [self loadGeneralTransactions:transArray
                         category:RootCategoryAll
                            where:[NSString stringWithFormat:
                                   @"credit_card_bill_id_fk = %ld and "
                                   @"category_code_fk & 255 != %d",
                                   bill.billId,
                                   RootCategoryTransfer]
                     beginAtIndex:0
                            limit:0
                       descending:NO];
}

- (void)creditCardBill:(RichCreditCardBill *)bill loadAssociatedPaymentsToArray:(NSMutableArray *)paymentArray database:(id *)database
{
    if (paymentArray == nil)
        return;
    
    [self loadGeneralTransactions:paymentArray
                         category:RootCategoryTransfer
                            where:[NSString stringWithFormat:
                                   @"credit_card_bill_id_fk = %ld",
                                   bill.billId]
                     beginAtIndex:0
                            limit:0
                       descending:NO];
}

- (void)creditCardBill:(RichCreditCardBill *)bill loadUnassociatedTransactionsToArray:(NSMutableArray *)transArray database:(id *)database
{
    if (transArray == nil)
        return;
    
    [self loadGeneralTransactions:transArray
                         category:RootCategoryAll
                            where:[NSString stringWithFormat:
                                   @"date <= \"%@\" and "
                                   @"(from_account_id_fk = %ld or to_account_id_fk = %ld) and "
                                   @"credit_card_bill_id_fk = %d and "
                                   @"category_code_fk & 255 != %d",
                                   [[RichDelegate core] stringFromDate:bill.closingDate format:@"yyyy-MM-dd 23:59:59"],
                                   bill.accountId, bill.accountId, CREDIT_CARD_BILL_ID_NONE, RootCategoryTransfer]
                     beginAtIndex:0
                            limit:0
                       descending:NO];
}

- (void)creditCardBill:(RichCreditCardBill *)bill loadUnassociatedPaymentsToArray:(NSMutableArray *)paymentArray database:(id *)database
{
    if (paymentArray == nil)
        return;
    
    [self loadGeneralTransactions:paymentArray
                         category:RootCategoryTransfer
                            where:[NSString stringWithFormat:
#if 0 /* Ignore payment date checking, the payment date can before the closing date */
                                   @"date > \"%@\" and "
#endif
                                   @"to_account_id_fk = %ld and "
                                   @"credit_card_bill_id_fk = %d",
#if 0
                                   [[RichDelegate core] stringFromDate:bill.closingDate format:@"yyyy-MM-dd 23:59:59"],
#endif
                                   bill.accountId, CREDIT_CARD_BILL_ID_NONE]
                     beginAtIndex:0
                            limit:0
                       descending:NO];
}

- (void)creditCardBill:(RichCreditCardBill *)bill disassociateTransactionsFromArray:(NSMutableArray *)transArray database:(id *)database
{
    if (transArray == nil)
        return;
    
    // Clear transactions related to this bill
    for (RichTransactionGeneral *__trans in transArray) {
        __trans.creditCardBillId = CREDIT_CARD_BILL_ID_NONE;
        if ([self updateTransaction:(id *)__trans type:kTransactionTypeGeneral] != RICH_OK)
            NSLog(@"Disassociate transaction (id = %lld) failed", __trans.transId);
    }
}

- (void)creditCardBill:(RichCreditCardBill *)bill saveTransactionsToDatabase:(id *)database
{
    if (!bill.transactions)
        return;
    
    for (RichTransactionGeneral *__trans in bill.transactions) {
        __trans.creditCardBillId = bill.billId;
        if ([self updateTransaction:(id *)__trans type:kTransactionTypeGeneral] != RICH_OK)
            NSLog(@"Save transaction (id = %lld) failed", __trans.transId);
    }
}

- (void)creditCardBill:(RichCreditCardBill *)bill savePaymentsToDatabase:(id *)database
{
    if (!bill.payments)
        return;
    
    for (RichTransactionGeneral *__payment in bill.payments) {
        __payment.creditCardBillId = bill.billId;
        if ([self updateTransaction:(id *)__payment type:kTransactionTypeGeneral] != RICH_OK)
            NSLog(@"Save payment (id = %lld) failed", __payment.transId);
    }
}

- (int)loadCreditCardBills
{
    SQLMResultSet *rs;
    RichCreditCardBill *bill;
    NSUInteger dataCount;
    
    for (NSNumber *kBillId in [creditCardBills allKeys]) {
        bill = [creditCardBills objectForKey:kBillId];
        if (bill.transactions)
            [bill.transactions removeAllObjects];
    }
    
    [creditCardBills removeAllObjects];
    
    rs = [sqliteManager selectColumns:@"*" fromTable:RichTableCreditCardBill where:nil orderBy:@"id_pk" descending:NO];
    
    if (rs == nil) return -ERROR_CORE_DB_QUERY;
    
    dataCount = [rs dataCount];
    
    if (dataCount == 0) {
        [rs release];
        goto exit;
    }
    
    while ([rs next]) {
        bill = [[RichCreditCardBill alloc] init];
        bill.billId       = [rs longForColumnIndex:0];
        bill.accountId    = [rs longForColumnIndex:1];
        bill.closingDate  = [self stringToDate:[rs stringForColumnIndex:2] format:nil];
        bill.dueDate      = [self stringToDate:[rs stringForColumnIndex:3] format:nil];
        bill.transactions = [[NSMutableArray alloc] init];
        bill.payments     = [[NSMutableArray alloc] init];
        
        [self creditCardBill:bill loadAssociatedTransactionsToArray:bill.transactions database:nil];
        [self creditCardBill:bill loadAssociatedPaymentsToArray:bill.payments database:nil];
        
        [creditCardBills setObject:bill forKey:[NSNumber numberWithUnsignedInteger:bill.billId]];
        
        [bill release];
    }
    
    [rs release];
    
exit:
    [[NSNotificationCenter defaultCenter] postNotificationName:RichCoreNotificationReloadCreditCardBill object:nil];
    
    return (int)dataCount;
}

- (int64_t)createCreditCardBill:(RichCreditCardBill *)bill
{
    if (bill == nil)
        return -ERROR_CORE_INVALID_CREDIT_CARD_BILL;
    
    if  (bill.accountId == 0)
        return -ERROR_CORE_INVALID_ACCOUNT;
    
    if (bill.closingDate == nil || bill.dueDate == nil)
        return -ERROR_CORE_INVALID_PARM;
    
    int64_t rowId = [sqliteManager insertColumns:@"account,"
                                                @"closing_date,"
                                                @"due_date"
                                      withValues:[NSString stringWithFormat:@"%ld, \"%@\", \"%@\"",
                                                  bill.accountId,
                                                  [self stringFromDate:bill.closingDate format:nil],
                                                  [self stringFromDate:bill.dueDate format:nil]]
                                       intoTable:RichTableCreditCardBill];
    
    if (rowId < 0) return rowId;
    
    bill.billId = rowId;
    
    [creditCardBills setObject:bill forKey:[NSNumber numberWithUnsignedInteger:bill.billId]];
    
    [self creditCardBill:bill saveTransactionsToDatabase:nil];
    [self creditCardBill:bill savePaymentsToDatabase:nil];
    
    return rowId;
}

- (int)updateCreditCardBill:(RichCreditCardBill *)bill
{
    if (bill == nil)
        return -ERROR_CORE_INVALID_CREDIT_CARD_BILL;
    
    if (bill.accountId == 0)
        return -ERROR_CORE_INVALID_ACCOUNT;
    
    if (bill.closingDate == nil || bill.dueDate == nil)
        return -ERROR_CORE_INVALID_PARM;
    
    //if (bill.payments == nil)
    //    return -ERROR_CORE_INVALID_TRANSACTION;
    
    if ([creditCardBills objectForKey:[NSNumber numberWithUnsignedInteger:bill.billId]]) {
        int result = [sqliteManager updateTable:RichTableCreditCardBill
                                     setColumns:[NSString stringWithFormat:
                                                 @"account = %ld,"
                                                 @"closing_date = \"%@\","
                                                 @"due_date = \"%@\"",
                                                 bill.accountId,
                                                 [self stringFromDate:bill.closingDate format:nil],
                                                 [self stringFromDate:bill.dueDate format:nil]]
                                          where:[NSString stringWithFormat:@"id_pk = %ld", bill.billId]];
        
        if (result == RICH_OK) {
            [self creditCardBill:bill saveTransactionsToDatabase:nil];
            [self creditCardBill:bill savePaymentsToDatabase:nil];
            return result;
        }
    }
    
    return -ERROR_CORE_INVALID_CREDIT_CARD_BILL;
}

- (int)deleteCreditCardBill:(RichCreditCardBill *)bill
{
    if (bill == nil || bill.billId == CREDIT_CARD_BILL_ID_NONE)
        return -ERROR_CORE_INVALID_CREDIT_CARD_BILL;
    
    if ([creditCardBills objectForKey:[NSNumber numberWithUnsignedInteger:bill.billId]]) {
        // Clear transactions related to this bill
        [self creditCardBill:bill disassociateTransactionsFromArray:bill.transactions database:nil];
        [self creditCardBill:bill disassociateTransactionsFromArray:bill.payments database:nil];
        
        [bill.transactions removeAllObjects];
        [bill.payments removeAllObjects];
        
        int result = [sqliteManager deleteFromTable:RichTableCreditCardBill
                                              where:[NSString stringWithFormat:@"id_pk = %ld", bill.billId]];
        
        if (result == RICH_OK)
            [creditCardBills removeObjectForKey:[NSNumber numberWithUnsignedInteger:bill.billId]];
        
        return result;
    }
    
    return -ERROR_CORE_INVALID_CREDIT_CARD_BILL;
}

#pragma mark - Common APIs

- (NSString *)stringFromDate:(NSDate *)date format:(NSString *)dateFormat
{
    if (dateFormat == nil)
        dateFormat = @"yyyy-MM-dd HH:mm:ss";
    
    NSDateFormatter *dateFormatter = [[NSDateFormatter alloc] init];
    [dateFormatter setDateFormat:dateFormat];
    NSString *str = [[dateFormatter stringFromDate:date] copy];
    [dateFormatter release];
    
    return str;
}

- (NSDate *)stringToDate:(NSString *)string format:(NSString *)dateFormat
{
    if (dateFormat == nil)
        dateFormat = @"yyyy-MM-dd HH:mm:ss";
    
    NSDateFormatter *dateFormatter = [[NSDateFormatter alloc] init];
    [dateFormatter setDateFormat:dateFormat];
    NSDate *date = [[dateFormatter dateFromString:string] copy];
    [dateFormatter release];
    
    return date;
}

@end
